USE [refunddb]
GO

/****** Object:  Table [dbo].[lzfy_password_update_tasks]    Script Date: 2025/1/15 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- 删除表（如果存在）
IF EXISTS (SELECT * FROM sysobjects WHERE name='lzfy_password_update_tasks' AND xtype='U')
    DROP TABLE [dbo].[lzfy_password_update_tasks]
GO

-- 创建患者退费密码更新定时任务表
CREATE TABLE [dbo].[lzfy_password_update_tasks] (
    [id] int IDENTITY(1,1) NOT NULL,
    [task_id] nvarchar(50) NOT NULL,
    [patient_id] nvarchar(20) NOT NULL,
    [verify_code] nvarchar(10) NOT NULL,
    [refund_password] nvarchar(100) NOT NULL,
    [modifier] nvarchar(50) NOT NULL,
    [modifier_card_type] nvarchar(10) NOT NULL DEFAULT '08',
    [modifier_card_code] nvarchar(50) NOT NULL,
    [scheduled_time] datetime NOT NULL,
    [created_time] datetime NOT NULL DEFAULT GETDATE(),
    [status] nvarchar(20) NOT NULL DEFAULT 'pending',
    [executed_time] datetime NULL,
    [error_message] ntext NULL,
    [retry_count] int NOT NULL DEFAULT 0,
    [max_retry_count] int NOT NULL DEFAULT 3,
    CONSTRAINT [PK_lzfy_password_update_tasks] PRIMARY KEY CLUSTERED ([id] ASC),
    CONSTRAINT [UK_task_id] UNIQUE NONCLUSTERED ([task_id] ASC)
)
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'主键ID,自增' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'lzfy_password_update_tasks', @level2type=N'COLUMN',@level2name=N'id'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'任务唯一标识符(GUID)' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'lzfy_password_update_tasks', @level2type=N'COLUMN',@level2name=N'task_id'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'患者ID' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'lzfy_password_update_tasks', @level2type=N'COLUMN',@level2name=N'patient_id'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'验证码' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'lzfy_password_update_tasks', @level2type=N'COLUMN',@level2name=N'verify_code'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'退费密码(MD5加密)' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'lzfy_password_update_tasks', @level2type=N'COLUMN',@level2name=N'refund_password'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'修改者用户名' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'lzfy_password_update_tasks', @level2type=N'COLUMN',@level2name=N'modifier'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'修改者证件类型' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'lzfy_password_update_tasks', @level2type=N'COLUMN',@level2name=N'modifier_card_type'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'修改者证件号码' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'lzfy_password_update_tasks', @level2type=N'COLUMN',@level2name=N'modifier_card_code'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'计划执行时间' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'lzfy_password_update_tasks', @level2type=N'COLUMN',@level2name=N'scheduled_time'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'任务创建时间' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'lzfy_password_update_tasks', @level2type=N'COLUMN',@level2name=N'created_time'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'任务状态: Pending(待执行), Executing(执行中), Completed(已完成), Failed(失败), Cancelled(已取消)' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'lzfy_password_update_tasks', @level2type=N'COLUMN',@level2name=N'status'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'实际执行时间' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'lzfy_password_update_tasks', @level2type=N'COLUMN',@level2name=N'executed_time'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'错误信息' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'lzfy_password_update_tasks', @level2type=N'COLUMN',@level2name=N'error_message'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'重试次数' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'lzfy_password_update_tasks', @level2type=N'COLUMN',@level2name=N'retry_count'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'最大重试次数' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'lzfy_password_update_tasks', @level2type=N'COLUMN',@level2name=N'max_retry_count'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'患者退费密码更新定时任务表' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'lzfy_password_update_tasks'
GO

-- 创建索引
CREATE NONCLUSTERED INDEX [IX_lzfy_password_update_tasks_patient_id] ON [dbo].[lzfy_password_update_tasks] ([patient_id] ASC)
GO

CREATE NONCLUSTERED INDEX [IX_lzfy_password_update_tasks_scheduled_time] ON [dbo].[lzfy_password_update_tasks] ([scheduled_time] ASC)
GO

CREATE NONCLUSTERED INDEX [IX_lzfy_password_update_tasks_status] ON [dbo].[lzfy_password_update_tasks] ([status] ASC)
GO

CREATE NONCLUSTERED INDEX [IX_lzfy_password_update_tasks_created_time] ON [dbo].[lzfy_password_update_tasks] ([created_time] ASC)
GO